package excel;

import com.microsoft.schemas.office.excel.CTClientData;
import com.microsoft.schemas.office.excel.STObjectType;
import com.microsoft.schemas.vml.CTShape;
import com.microsoft.schemas.vml.CTTextbox;
import com.spire.xls.core.ICheckBox;
import com.spire.xls.core.ICheckBoxes;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.record.CommonObjectDataSubRecord;
import org.apache.poi.hssf.record.ObjRecord;
import org.apache.poi.hssf.record.SubRecord;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.ZipPackagePart;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.UserModelService;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFVMLDrawing;
import org.apache.xmlbeans.*;
import org.apache.xmlbeans.impl.values.TypeStoreUser;
import org.apache.xmlbeans.impl.values.XmlAnyTypeImpl;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTControl;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTControls;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLegacyDrawing;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorksheetImpl;

import javax.xml.namespace.QName;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigInteger;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 可能需要微调，不同的excel版本生成的文件，xml格式有细微的差别 元素标签、属性值之类的
 */
public class ExcelCheckboxUtil {

    // 复选框选中名称 key
    private static final String CHECK_LABEL_NAMES_KEY = "check_label_name";
    // 复选框选中左上角单元格 key
    private static final String CHECK_CELL_KEY = "check_cell";

    public static void main(String[] args) throws Exception {

        spireXlsx("Excel3.xlsx");

        // String pathFile = "C:\\Users\\74102\\Desktop\\报告(1).xls";
        String pathFile = "C:\\Users\\74102\\Desktop\\报告(1).xlsx";
        // String pathFile = "Excel4.xls";
        try (FileInputStream fileInputStream = new FileInputStream(pathFile);
             Workbook wb = WorkbookFactory.create(fileInputStream);
        ) {
            FileOutputStream out = null;
            Sheet sheet = wb.getSheetAt(0);
            // 如果设置了关联单元格，需要将关联单元格清除才能生效 关联单元格优先级最高
            // 目前不支持匹配空格
            List<String> checkLabelNamesList = Arrays.asList("拒绝");
            Map<String, List<String>> paramMap = new HashMap<>();
            paramMap.put(CHECK_LABEL_NAMES_KEY, checkLabelNamesList);

            // 未设置名称的复选框如何选中？？？ shapeId anchor
            // 左上角单元格cell 行row_列col 从0开始
            List<String> checkCellList = Arrays.asList("32_0", "33_0");
            paramMap.put(CHECK_CELL_KEY, checkCellList);

            if (sheet instanceof HSSFSheet) {
                // xls
                handleControlsXls((HSSFSheet) sheet, paramMap);
                out = new FileOutputStream("Excel4.xls");
            } else if (sheet instanceof XSSFSheet) {
                // xlsx
                // 获取所有复选框选中状态
                List<Map<String, String>> checkboxXlsx = getCheckboxXlsx((XSSFSheet) sheet);

                setCheckboxXlsx((XSSFSheet) sheet, paramMap);

                out = new FileOutputStream("Excel4.xlsx");
            }

            if (out != null) {
                // 修改输出到新文件
                wb.write(out);
            }
            out.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 处理表单控件
     *
     * @param sheet
     * @param paramMap 参数
     * @throws Exception
     */
    private static void handleControlsXls(HSSFSheet sheet, Map<String, List<String>> paramMap) throws Exception {
        if (sheet == null) {
            return;
        }
        HSSFPatriarch drawingPatriarch = sheet.getDrawingPatriarch();
        if (drawingPatriarch == null) {
            return;
        }
        List<HSSFShape> hSSFShapes = drawingPatriarch.getChildren();
        if (CollectionUtils.isEmpty(hSSFShapes)) {
            return;
        }

        // 勾选复选框 先把模板所有复选框都置为未选中，且不要设置关联单元格
        setCheckboxXls(hSSFShapes, paramMap);

        // 获取所有复选框选中状态
        List<Map<String, String>> checkboxList = getCheckboxXls(hSSFShapes);
        System.out.println();

    }

    /**
     * 勾选复选框 xls
     *
     * @param hSSFShapes
     * @param paramMap   参数，目前支持按名称 或 按cell位置（左上角单元格cell 行row_列col 从0开始）
     * @throws Exception
     */
    private static void setCheckboxXls(List<HSSFShape> hSSFShapes, Map<String, List<String>> paramMap) throws Exception {
        if (MapUtils.isEmpty(paramMap)) {
            return;
        }
        for (HSSFShape hSSFShape : hSSFShapes) {
            if (hSSFShape instanceof HSSFSimpleShape) {
                HSSFSimpleShape simpleShape = (HSSFSimpleShape) hSSFShape;

                Field _objRecord = HSSFShape.class.getDeclaredField("_objRecord");
                _objRecord.setAccessible(true);
                ObjRecord objRecord = (ObjRecord) _objRecord.get(hSSFShape);

                // int shapeId = simpleShape.getShapeId();
                // System.out.println("shapeId:" + shapeId);

                if (objRecord == null) {
                    continue;
                }

                List<SubRecord> subRecords = objRecord.getSubRecords();
                if (CollectionUtils.isEmpty(subRecords)) {
                    continue;
                }

                // 是否复选框
                boolean isCheckbox = false;
                for (SubRecord subRecord : subRecords) {
                    if (subRecord instanceof CommonObjectDataSubRecord) {
                        CommonObjectDataSubRecord dataSubRecord = (CommonObjectDataSubRecord) subRecord;
                        if (dataSubRecord.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_CHECKBOX) {
                            // 复选框
                            isCheckbox = true;
                            break;
                        }
                    }
                }
                if (!isCheckbox) {
                    continue;
                }


                String labelName = simpleShape.getString().getString();
                List<String> checkLabelNames = paramMap.get(CHECK_LABEL_NAMES_KEY);
                List<String> checkCells = paramMap.get(CHECK_CELL_KEY);

                // 是否勾选
                boolean isChecked = false;
                // 通过名称判断
                if (CollectionUtils.isNotEmpty(checkLabelNames) && checkLabelNames.contains(labelName)) {
                    isChecked = true;
                }
                // 通过左上角单元格位置判断
                if (!isChecked && CollectionUtils.isNotEmpty(checkCells)) {
                    HSSFAnchor anchor = simpleShape.getAnchor();
                    if (anchor instanceof HSSFClientAnchor) {
                        HSSFClientAnchor clientAnchor = (HSSFClientAnchor) anchor;
                        int row1 = clientAnchor.getRow1();
                        short col1 = clientAnchor.getCol1();
                        // 左上角单元格cell 行row_列col 从0开始
                        if (checkCells.contains(row1 + "_" + col1)) {
                            isChecked = true;
                        }
                    }

                }
                System.out.println(labelName);

                if (!isChecked) {
                    // 不勾选
                    continue;
                }

                // 勾选
                for (SubRecord subRecord : subRecords) {
                    if (!(subRecord instanceof CommonObjectDataSubRecord)) {
                        String subRecordText = subRecord.toString();
                        System.out.println(subRecordText);
                        if (subRecordText.indexOf("sid=0x000A") > -1 || subRecordText.indexOf("sid=0x0012") > -1) {
                            try {
                                // java 反射获取内部静态类 https://blog.51cto.com/u_16213343/7067111
                                Class outerClass = SubRecord.class;
                                Class innerClass = outerClass.getDeclaredClasses()[0];
                                // 使用内部静态类的Class对象操作类的属性和方法
                                Field field = innerClass.getDeclaredField("_data");
                                field.setAccessible(true);
                                byte[] _data = (byte[]) field.get(subRecord);

                                _data[0] = 1;
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }

                }

                System.out.println();
            }

            System.out.println();
        }

    }

    /**
     * 勾选复选框 xlsx
     *
     * @param sheet
     * @param paramMap 参数，目前支持按名称 或 按cell位置（左上角单元格cell 行row_列col 从0开始）
     * @throws Exception
     */
    private static void setCheckboxXlsx(XSSFSheet sheet, Map<String, List<String>> paramMap) throws Exception {
        if (sheet == null || MapUtils.isEmpty(paramMap)) {
            return;
        }

        CTWorksheet ctWorksheet = sheet.getCTWorksheet();
        CTWorksheetImpl ctWorksheetImpl = (CTWorksheetImpl) ctWorksheet;

        CTLegacyDrawing legacyDrawing = ctWorksheet.getLegacyDrawing();
        if (legacyDrawing == null) {
            return;
        }
        String legacyDrawingId = legacyDrawing.getId();
        POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);

        // 2种画布 普通 XSSFDrawing (drawings/drawing1.xml) 和vml XSSFVMLDrawing (drawings/vmlDrawing1.vml)
        if (!(part instanceof XSSFVMLDrawing)) {
            return;
        }

        List<Map<String, Object>> control2RidListV1 = getControl2RidListV1(ctWorksheetImpl);
        List<Map<String, Object>> control2RidListV2 = getControl2RidListV2(ctWorksheetImpl);

        // relations一致
        // Name: /xl/drawings/vmlDrawing1.vml - Content Type: application/vnd.openxmlformats-officedocument.vmlDrawing
        XSSFVMLDrawing vml = (XSSFVMLDrawing) part;

        // xl\drawings\vmlDrawing1.vml 读取的是这个文件的内容 <v:shape
        Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
        _items.setAccessible(true);
        @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
        List<XmlObject> items = (List<XmlObject>) _items.get(vml);

        if (CollectionUtils.isEmpty(items)) {
            return;
        }
        for (XmlObject xo : items) {
            if (xo instanceof CTShape) {
                CTShape ctShape = (CTShape) xo;
                // https://www.saoniuhuo.com/article/detail-465325.html
                List<CTClientData> clientDataList = ctShape.getClientDataList();
                // 为什么会有好几种格式？？？ 有些id 有些spid
                // <v:shape id="_x0000_s1025" o:spt="201" type="#_x0000_t201"
                // <xml-fragment coordsize="21600,21600" filled="f" id="Check Box 102" o:insetmode="auto" o:spid="_x0000_s6246"
                String shapeId = ctShape.getId();
                String spid = ctShape.getSpid();

                if (CollectionUtils.isNotEmpty(clientDataList)) {
                    CTClientData clientData = clientDataList.get(0);
                    // 类型
                    STObjectType.Enum type = clientData.getObjectType();
                    if (!STObjectType.CHECKBOX.equals(type)) {
                        continue;
                    }

                    String labelName = getLabelName(ctShape);
                    labelName = labelName.replaceAll("[\\s*|\t|\r|\n]", "");

                    List<String> checkLabelNames = paramMap.get(CHECK_LABEL_NAMES_KEY);
                    List<String> checkCells = paramMap.get(CHECK_CELL_KEY);

                    // 是否勾选
                    boolean isChecked = false;
                    // 通过名称判断
                    if (CollectionUtils.isNotEmpty(checkLabelNames) && checkLabelNames.contains(labelName)) {
                        isChecked = true;
                    }
                    // 通过左上角单元格位置判断
                    if (!isChecked && CollectionUtils.isNotEmpty(checkCells)) {
                        // col1,dx1,row1,dy1,col2,dx2,row2,dy2
                        String[] anchorArray = clientData.getAnchorArray();
                        if (anchorArray != null && anchorArray.length > 0) {
                            String anchorStr = anchorArray[0];

                            String[] split = anchorStr.trim().split(",");
                            int[] ints = Arrays.stream(split).mapToInt(o -> Integer.parseInt(o.trim())).toArray();
                            // 左上角单元格cell 行row_列col 从0开始
                            System.out.println("cell:" + ints[2] + "_" + ints[0]);
                            if (checkCells.contains(ints[2] + "_" + ints[0])) {
                                isChecked = true;
                            }
                        }
                    }
                    if (!isChecked) {
                        // 不勾选
                        continue;
                    }

                    System.out.println("checked: " + labelName);
                    // 复选框选中
                    selectOrCancel(clientData, 1, control2RidListV1, sheet, shapeId, spid);

                }

                System.out.println("================");
            }


        }

    }

    /**
     * 获取表单控件与rid对应 方式一
     * 通过CTControls 需要引入ooxml-schemas包
     *
     * @param ctWorksheetImpl
     * @throws XmlException
     */
    private static List<Map<String, Object>> getControl2RidListV1(CTWorksheetImpl ctWorksheetImpl) throws XmlException {
        // 表单控件与rid对应
        List<Map<String, Object>> control2RidList = new ArrayList<>();
        // xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        /**
         * xl\worksheets\sheet1.xml
         *     <drawing r:id="rId1"/>
         *     <legacyDrawing r:id="rId2"/>
         *     <mc:AlternateContent>
         *         <mc:Choice Requires="x14">
         *             <controls>
         *                 <mc:AlternateContent>
         *                     <mc:Choice Requires="x14">
         *                         <control shapeId="1025" name="Check Box 1" r:id="rId3">
         *                             <controlPr defaultSize="0">
         *                                 <anchor moveWithCells="1">
         */
        // <mc:AlternateContent>
        QName alternateContentQName = new QName("http://schemas.openxmlformats.org/markup-compatibility/2006", "AlternateContent");
        // <controls>
        QName controlsQName = new QName("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "controls", "main");
        // 只能筛选一级元素
        TypeStoreUser alternateElement = ctWorksheetImpl.get_store().find_element_user(alternateContentQName, 0);

        TypeStoreUser choice = alternateElement.get_store().find_element_user(
                new QName("http://schemas.openxmlformats.org/markup-compatibility/2006", "Choice"), 0);
        TypeStoreUser controls = choice.get_store().find_element_user(controlsQName, 0);
        // <xml-fragment xmlns:mc=...><mc:AlternateContent><mc:Choice Requires="x14"><main:control shapeId="1025"
        // </mc:Choice></mc:AlternateContent><mc:AlternateContent><mc:Choice Requires="x14"><main:control
        // </main:controlPr></main:control></mc:Choice></mc:AlternateContent></xml-fragment>
        // ?只匹配到一个
        String replace = ((XmlAnyTypeImpl) controls).xmlText().replaceAll("(<xml-fragment.*?>)(.*?)(<main:control)", "$1$3");
        replace = replace.replaceAll("(</main:control>)(.*?)(<main:control)", "$1$3");
        // 最后一个</main:control>到结尾
        replace = replace.replaceAll("(.*</main:control>)(.*?)(</xml-fragment>)", "$1$3");

        // CTControls 需要引入ooxml-schemas包
        CTControls ctControls = CTControls.Factory.parse(replace);
        if (ctControls != null && CollectionUtils.isNotEmpty(ctControls.getControlList())) {
            for (CTControl ctControl : ctControls.getControlList()) {
                long shapeId = ctControl.getShapeId();
                String name = ctControl.getName();
                String rid = ctControl.getId();

                Map<String, Object> map = new HashMap<>();
                control2RidList.add(map);
                map.put("shapeId", shapeId);
                map.put("name", name);
                map.put("rid", rid);
            }
        }

        return control2RidList;
    }

    /**
     * 获取表单控件与rid对应 方式二
     * 手动解析xml
     *
     * @param ctWorksheetImpl
     * @throws XmlException
     */
    private static List<Map<String, Object>> getControl2RidListV2(CTWorksheetImpl ctWorksheetImpl) throws XmlException {
        // 表单控件与rid对应
        List<Map<String, Object>> control2RidList = new ArrayList<>();
        // xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        /**
         * xl\worksheets\sheet1.xml
         *     <drawing r:id="rId1"/>
         *     <legacyDrawing r:id="rId2"/>
         *     <mc:AlternateContent>
         *         <mc:Choice Requires="x14">
         *             <controls>
         *                 <mc:AlternateContent>
         *                     <mc:Choice Requires="x14">
         *                         <control shapeId="1025" name="Check Box 1" r:id="rId3">
         *                             <controlPr defaultSize="0">
         *                                 <anchor moveWithCells="1">
         */
        // 手动解析xml
        XmlCursor xmlCursor = ctWorksheetImpl.get_store().new_cursor();

        // <control shapeId="1025" name="Check Box 1" r:id="rId3">
        // control元素
        QName controlQName = new QName("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "control", "main");
        // control元素的r:id属性
        QName ridQName = new QName("http://schemas.openxmlformats.org/officeDocument/2006/relationships", "id", "r");

        // 查找control标签，解析属性
        while (xmlCursor.hasNextToken()) {
            XmlCursor.TokenType tokenType = xmlCursor.toNextToken();
            /*
                <control shapeId="1025" name="Check Box 1" r:id="rId3">
                    <controlPr defaultSize="0">
            */
            // System.out.println("xmlCursor.getName(): " + xmlCursor.getName());
            if (tokenType.isStart()) {
                if (controlQName.equals(xmlCursor.getName())) {
                    Map<String, Object> map = new HashMap<>();
                    control2RidList.add(map);

                    // 解析属性值
                    // <xml-fragment shapeId="1025" name="Check Box 1" r:id="rId3"
                    while (xmlCursor.hasNextToken()) {
                        tokenType = xmlCursor.toNextToken();
                        if (tokenType.isAttr()) {
                            QName name = xmlCursor.getName();
                            String namespaceURI = name.getNamespaceURI();
                            String localPart = name.getLocalPart();
                            String prefix = name.getPrefix();
                            if (new QName("shapeId").equals(xmlCursor.getName())) {
                                // System.out.println("shapeId: " + xmlCursor.getTextValue());
                                map.put("shapeId", xmlCursor.getTextValue());
                            }
                            if (new QName("name").equals(xmlCursor.getName())) {
                                // System.out.println("name: " + xmlCursor.getTextValue());
                                map.put("name", xmlCursor.getTextValue());
                            }
                            // {http://schemas.openxmlformats.org/officeDocument/2006/relationships}id
                            if (ridQName.equals(xmlCursor.getName())) {
                                // System.out.println("rid: " + xmlCursor.getTextValue());
                                map.put("rid", xmlCursor.getTextValue());
                            }
                        } else {
                            break;
                        }
                    }

                }
            }
        }

        return control2RidList;
    }

    /**
     * 取消或选中复选框
     *
     * @param type            0-取消选中，1-选中
     * @param control2RidList
     * @param sheet
     * @param shapeId
     */
    private static void selectOrCancel(CTClientData clientData, int type,
                                       List<Map<String, Object>> control2RidList, XSSFSheet sheet,
                                       String shapeId, String spid) {
        BigInteger[] checkedArray = clientData.getCheckedArray();
        // 复选框 或者 单选框
        String objectType = clientData.getObjectType().toString();
        if (type == 0) {
            // 取消选中
            if (checkedArray != null && checkedArray.length > 0) {
                // 没有效果 文件里面状态确实改变了，但是页面效果没有改变
                // clientData.removeChecked(0);
                // clientData.addChecked(BigInteger.ZERO);
                clientData.setCheckedArray(0, BigInteger.ZERO);

                // 修改对应的控制文件 不会自动修改 excel根据这个文件显示选择效果
                // 有些有这个文件 有些没有
                replaceXmlFile(type, control2RidList, sheet, shapeId, spid, objectType);
            }
        }

        /**
         * <v:shape>
         *     <v:textbox o:singleclick="f">复选框</v:textbox>
         *     <x:ClientData ObjectType="Checkbox">
         *         <x:Anchor>1, 1, 4, 3, 1, 86, 5, 13</x:Anchor>
         *         <x:Checked>1</x:Checked>
         *     </x:ClientData>
         * </v:shape>
         */
        // xl\drawings\vmlDrawing1.vml 修改的是这个文件里面的<x:Checked>1</x:Checked>
        /**
         * <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
         * <formControlPr xmlns="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" objectType="CheckBox" checked="Checked" noThreeD="1"/>
         */
        // xl\ctrlProps\ctrlProp1.xml 但是复选框的实际选中效果是由这个文件的 checked="Checked" 控制的，每个表单控件单独一个文件

        /**
         * <?xml version="1.0" encoding="UTF-8" standalone="no"?>
         * <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
         * <Relationship Id="rId1" Target="../drawings/drawing1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"/>
         * <Relationship Id="rId2" Target="../drawings/vmlDrawing1.vml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing"/>
         * <Relationship Id="rId3" Target="../ctrlProps/ctrlProp1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId4" Target="../ctrlProps/ctrlProp2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId5" Target="../ctrlProps/ctrlProp3.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId6" Target="../ctrlProps/ctrlProp4.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId7" Target="../ctrlProps/ctrlProp5.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * </Relationships>
         */
        // xl\worksheets\_rels\sheet1.xml.rels 在这个文件 汇总引入
        if (type == 1) {
            // 选中
            if (checkedArray == null || checkedArray.length == 0) {
                clientData.insertChecked(0, BigInteger.ONE);

                // 修改对应的控制文件 不会自动修改 excel根据这个文件显示选择效果
                replaceXmlFile(type, control2RidList, sheet, shapeId, spid, objectType);
            }
        }
    }

    /**
     * 替换xml文件内容
     *
     * @param type            0-取消选中，1-选中
     * @param control2RidList
     * @param sheet
     * @param shapeId
     * @param objectType
     */
    private static void replaceXmlFile(int type, List<Map<String, Object>> control2RidList, XSSFSheet sheet,
                                       String shapeId, String spid, String objectType) {
        if (CollectionUtils.isEmpty(control2RidList)) {
            return;
        }
        // <v:shape id="_x0000_s1025"
        // <xml-fragment coordsize="21600,21600" filled="f" id="Check Box 102" o:insetmode="auto" o:spid="_x0000_s6246"
        // https://learn.microsoft.com/zh-cn/openspecs/office_standards/ms-xlsx/24baf70c-49ec-4d3c-81fa-133e8681490d
        shapeId = spid != null && spid.startsWith("_x0000_s") ? spid : shapeId;
        String shapeIdTmp = shapeId.replace("_x0000_s", "");
        Map<String, Object> control2Rid = control2RidList.stream()
                .filter(o -> o.get("shapeId").toString().equals(shapeIdTmp))
                .findFirst().orElse(null);

        if (control2Rid == null || control2Rid.get("rid") == null) {
            return;
        }

        String rid = control2Rid.get("rid").toString();
        List<POIXMLDocumentPart.RelationPart> relationParts = sheet.getRelationParts();
        for (POIXMLDocumentPart.RelationPart relationPart : relationParts) {
            PackageRelationship relationship = relationPart.getRelationship();
            POIXMLDocumentPart documentPart = relationPart.getDocumentPart();

            // rid和控制文件对应
            if (relationship.getId().equals(rid)) {
                if (documentPart.toString().indexOf("application/vnd.ms-excel.controlproperties+xml") > -1) {
                    PackagePart packagePart = documentPart.getPackagePart();

                    ZipPackagePart zipPackagePart = (ZipPackagePart) packagePart;
                    PackagePartName partName = zipPackagePart.getPartName();

                    try (InputStream inputStream = packagePart.getInputStream();) {
                        String result = IOUtils.toString(inputStream, StandardCharsets.UTF_8);

                        /**
                         * <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                         * <formControlPr xmlns="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" objectType="CheckBox" fmlaLink="Sheet2!$F$2" noThreeD="1" val="0"/>
                         *
                         * fmlaLink 关联单元格 FmlaLink (链接公式)
                         * https://learn.microsoft.com/zh-cn/dotnet/api/documentformat.openxml.vml.spreadsheet.clientdata?view=openxml-2.8.1&redirectedfrom=MSDN
                         * https://learn.microsoft.com/zh-hk/dotnet/api/documentformat.openxml.office2010.excel.formcontrolproperties.fmlalink?view=openxml-2.8.1
                         */
                        System.out.println(result);

                        if (type == 0) {
                            // 取消选中
                            if (result.indexOf("checked=\"Checked\"") > -1) {
                                String replace = result.replace("checked=\"Checked\"", "");

                                // 回写
                                try (OutputStream outputStream = packagePart.getOutputStream();) {
                                    IOUtils.write(replace, outputStream);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }

                            }
                        } else if (type == 1) {
                            // 选中
                            if (result.indexOf("checked=\"Checked\"") < 0) {
                                // 匹配不到为什么？？？ 字符串大小写不一致
                                objectType = "Checkbox".equals(objectType) ? "CheckBox" : objectType;
                                String srcStr = "objectType=\"" + objectType + "\"";
                                String targetStr = srcStr + " checked=\"Checked\"";

                                String replace = result.replace(srcStr, targetStr);

                                // 回写
                                try (OutputStream outputStream = packagePart.getOutputStream();) {
                                    IOUtils.write(replace, outputStream);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }

                            }
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }

                break;
            }

        }
    }


    /**
     * 获取所有复选框选中状态 xls
     *
     * @param hSSFShapes
     * @throws Exception
     */
    private static List<Map<String, String>> getCheckboxXls(List<HSSFShape> hSSFShapes) throws Exception {
        List<Map<String, String>> list = new ArrayList<>();
        for (HSSFShape hSSFShape : hSSFShapes) {
            if (hSSFShape instanceof HSSFSimpleShape) {
                HSSFSimpleShape simpleShape = (HSSFSimpleShape) hSSFShape;

                Field _objRecord = HSSFShape.class.getDeclaredField("_objRecord");
                _objRecord.setAccessible(true);
                ObjRecord objRecord = (ObjRecord) _objRecord.get(hSSFShape);

                // int shapeId = simpleShape.getShapeId();
                // System.out.println("shapeId:" + shapeId);

                if (objRecord == null) {
                    continue;
                }

                List<SubRecord> subRecords = objRecord.getSubRecords();
                if (CollectionUtils.isEmpty(subRecords)) {
                    continue;
                }

                // 是否复选框
                boolean isCheckbox = false;
                for (SubRecord subRecord : subRecords) {
                    if (subRecord instanceof CommonObjectDataSubRecord) {
                        CommonObjectDataSubRecord dataSubRecord = (CommonObjectDataSubRecord) subRecord;
                        if (dataSubRecord.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_CHECKBOX) {
                            // 复选框
                            isCheckbox = true;
                            break;
                        }
                    }
                }
                if (!isCheckbox) {
                    continue;
                }

                Map<String, String> map = new HashMap();
                list.add(map);

                String labelName = simpleShape.getString().getString();
                System.out.println(labelName);
                map.put("labelName", labelName);

                HSSFAnchor anchor = simpleShape.getAnchor();
                if (anchor instanceof HSSFClientAnchor) {
                    HSSFClientAnchor clientAnchor = (HSSFClientAnchor) anchor;
                    int row1 = clientAnchor.getRow1();
                    short col1 = clientAnchor.getCol1();
                    // 左上角单元格cell 行row_列col 从0开始
                    System.out.println("cell:" + row1 + "_" + col1);
                    map.put("cell", row1 + "_" + col1);
                }

                // 勾选
                for (SubRecord subRecord : subRecords) {
                    if (!(subRecord instanceof CommonObjectDataSubRecord)) {
                        String subRecordText = subRecord.toString();
                        System.out.println(subRecordText);

                        Pattern P = Pattern.compile("\\[sid=0x000A.+?\\[0(\\d),");
                        Matcher m = P.matcher(subRecord.toString());

                        if (m.find()) {
                            // org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x000A size=12 : [01, 00, 20, E6, 00, 00, 00, 00, 00, 00, 03, 00]]
                            String checkBit = m.group(1);
                            // if (checkBit.length() == 1) {
                            // }
                            map.put("checked", checkBit);
                            break;
                        }

                        /*if (subRecordText.indexOf("sid=0x000A") > -1) {
                            try {
                                // java 反射获取内部静态类 https://blog.51cto.com/u_16213343/7067111
                                Class outerClass = SubRecord.class;
                                Class innerClass = outerClass.getDeclaredClasses()[0];
                                // 使用内部静态类的Class对象操作类的属性和方法
                                Field field = innerClass.getDeclaredField("_data");
                                field.setAccessible(true);
                                byte[] _data = (byte[]) field.get(subRecord);

                                // 1-勾选，0-未勾选
                                map.put("checked", _data[0] + "");
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }*/
                    }

                }

                System.out.println();
            }

            System.out.println();
        }

        return list;
    }

    /**
     * 获取所有复选框选中状态 xlsx
     * 如何获取从Excel工作表中选择的复选框值 xlsx https://www.52dianzi.com/category/article/37/788341.html
     *
     * @param sheet
     */
    private static List<Map<String, String>> getCheckboxXlsx(XSSFSheet sheet) throws Exception {
        List<Map<String, String>> controlList = new ArrayList<>();
        if (sheet == null) {
            return controlList;
        }
        CTWorksheet ctWorksheet = sheet.getCTWorksheet();
        CTLegacyDrawing legacyDrawing = ctWorksheet.getLegacyDrawing();
        if (legacyDrawing == null) {
            return controlList;
        }
        String legacyDrawingId = legacyDrawing.getId();
        POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);

        // 2种画布 普通 XSSFDrawing (drawings/drawing1.xml) 和vml XSSFVMLDrawing (drawings/vmlDrawing1.vml)
        if (!(part instanceof XSSFVMLDrawing)) {
            return controlList;
        }

        // relations一致
        // Name: /xl/drawings/vmlDrawing1.vml - Content Type: application/vnd.openxmlformats-officedocument.vmlDrawing
        XSSFVMLDrawing vml = (XSSFVMLDrawing) part;

        // xl\drawings\vmlDrawing1.vml 读取的是这个文件的内容 <v:shape
        // 反射调用对象的protected方法
        /*Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
        _items.setAccessible(true);
        @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
        List<XmlObject> items = (List<XmlObject>) _items.get(vml);*/

        // Java 访问修饰符 https://www.cnblogs.com/jwyqn/p/16283111.html
        // 利用protected方法可以被同一包中的类或其他包中的子类所访问的特性
        // 建一个类，包名一致，欺骗它是在同一个包里面
        UserModelService userModelService = new UserModelService();
        List<XmlObject> items = userModelService.getItems(vml);

        if (CollectionUtils.isEmpty(items)) {
            return controlList;
        }
        for (XmlObject xo : items) {
            if (xo instanceof CTShape) {
                CTShape ctShape = (CTShape) xo;
                // https://www.saoniuhuo.com/article/detail-465325.html
                List<CTClientData> clientDataList = ctShape.getClientDataList();
                // <v:shape id="_x0000_s1025" o:spt="201" type="#_x0000_t201"
                String shapeId = ctShape.getId();

                if (CollectionUtils.isNotEmpty(clientDataList)) {
                    CTClientData clientData = clientDataList.get(0);
                    // 类型
                    STObjectType.Enum type = clientData.getObjectType();
                    if (STObjectType.CHECKBOX.equals(type) || STObjectType.RADIO.equals(type)) {
                        // 复选框 或者 单选框
                        String objectType = type.toString();

                        Map<String, String> map = new HashMap();
                        controlList.add(map);
                        map.put("type", objectType); // Checkbox Radio

                        BigInteger[] checkedArray = clientData.getCheckedArray();
                        // col1,dx1,row1,dy1,col2,dx2,row2,dy2
                        String[] anchorArray = clientData.getAnchorArray();

                        String labelName = getLabelName(ctShape);
                        map.put("labelName", labelName);

                        BigInteger checked = BigInteger.ZERO;
                        if (checkedArray != null && checkedArray.length > 0) {
                            checked = clientData.getCheckedArray(0);
                        }
                        map.put("checked", checked.toString());

                        if (anchorArray != null && anchorArray.length > 0) {
                            String anchorStr = anchorArray[0];

                            String[] split = anchorStr.trim().split(",");
                            int[] ints = Arrays.stream(split).mapToInt(o -> Integer.parseInt(o.trim())).toArray();
                            // 左上角单元格cell 行row_列col 从0开始
                            System.out.println("cell:" + ints[2] + "_" + ints[0]);
                            map.put("cell", ints[2] + "_" + ints[0]);
                        }

                    }
                }

                System.out.println("================");
            }


        }

        return controlList;
    }


    public static String getLabelName(CTShape ctShape) {
        String labelName = null;
        CTTextbox[] textboxArray = ctShape.getTextboxArray();
        if (textboxArray != null && textboxArray.length > 0) {
            CTTextbox ctTextbox = textboxArray[0];
            // 设置进去的时候就不是纯文本
            labelName = StripHT(ctTextbox.xmlText());
            System.out.println(labelName);
        }
        return labelName;
    }


    /**
     * Java实现从Html文本中提取纯文本的方法
     * https://www.jb51.net/article/140548.htm
     */
    public static String StripHT(String strHtml) {
        // 剔出<html>的标签
        String txtcontent = strHtml.replaceAll("</?[^>]+>", "");
        // 去除字符串中的空格,回车,换行符,制表符
        // \s代表一个空格 ，\s*对应零个或多个空格
        txtcontent = txtcontent.replaceAll("[\\s*|\t|\r|\n]", "");
        // 只需截除首尾空格 解析出来的空格，与展示出来的空格有差异，展示的时候似乎多个空格只显示一个
        // txtcontent = txtcontent.replaceAll("[\t|\r|\n]", "").trim();
        return txtcontent;
    }

    /**
     * 获取、添加复选框
     * Java 获取Excel中的表单控件 https://blog.csdn.net/eiceblue/article/details/124999259
     * Java 添加、删除Excel表单控件 https://www.cnblogs.com/Yesi/p/12610905.html
     * Excel .NET组件Spire.XLS教程：插入和删除表单控件 https://www.evget.com/article/2019/11/22/33558.html
     * @throws Exception
     */
    public static void spireXlsx(String pathFile) throws Exception {
        // 创建Workbook类的实例，加载Excel文档
        com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
        wb.loadFromFile(pathFile);

        //获取第1张工作表
        com.spire.xls.Worksheet sheet = wb.getWorksheets().get(0);
        ICheckBoxes checkBoxes = sheet.getCheckBoxes();
        int count = checkBoxes.getCount();
        for (int i = 0; i < count; i++) {
            ICheckBox checkBox = checkBoxes.get(i);
            System.out.println(checkBox.getText());
            System.out.println(checkBox.getCheckState());
            System.out.println(checkBox.getAlternativeText());
        }

        // public ICheckBox addCheckBox(int row, int column, int height, int width) {
        // ICheckBox checkBox = checkBoxes.addCheckBox(10, 10, 10, 10); // 高度：0.21 厘米 宽度： 0.21 厘米
        ICheckBox checkBox = checkBoxes.addCheckBox(10, 10, 18, 65); // 高度：0.49 厘米  宽度：1.78 厘米
        checkBox.setText("22222222");

        // 修改输出到新文件
        wb.saveToFile("Excel33.xlsx");
    }

}
